﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Data.SqlClient;
using System.Data;
using PermitsAndVisasLeadsSyncher.Helpers.Logging;
using PermitsAndVisasLeadsSyncher.Data_Layer;

namespace PermitsAndVisasLeadsSyncher.Helpers
{
   internal static class DBHelper
    {
       private static ILogger logger = LeadsSyncherLogger.CreateLogger();
       private static DataTable dataTable;
       private static SqlDataAdapter sqlDataAdapter;

       private static void CreateDataTable()
       {
           dataTable = new DataTable();
       }

       private static void CreateSqlDataAdapter()
       {
           sqlDataAdapter = new SqlDataAdapter();
       }

       public static void ExecuteSqlQuery(string query)
       {
           SqlConnection sqlConnection = DataAccess.OpenConnection();
           SqlCommand command = sqlConnection.CreateCommand();
           ExecuteSqlQuery(query, command);
       }

       public static string ExecuteSqlScalar(string query, SqlCommand command)
       {
           string output=string .Empty ;
           try
           {
               output=((int)command.ExecuteScalar()).ToString();
           }
           catch (Exception ex)
           {               
               throw new SqlExecutionException(query,ex );
           }
           return output;           
       }

       public static void ExecuteSqlQuery(string query, SqlCommand command)
       {
           command.CommandText = query;
           try
           {
               command.ExecuteNonQuery();
           }
           catch (Exception ex)
           {
               throw new SqlExecutionException(query, ex);
           }
           
       }

       public static DataTable GetDataTable(string MainQuery)
       {
           try
           {
               SqlConnection sqlConnection = DataAccess.OpenConnection();
               CreateDataTable();
               CreateSqlDataAdapter();
               var command = sqlConnection.CreateCommand();
               command.CommandText = MainQuery;
               sqlDataAdapter.SelectCommand = command;
               sqlDataAdapter.Fill(dataTable);
               return dataTable;
           }
           catch (Exception ex)
           {
               throw new SqlExecutionException(MainQuery, ex);               
           }
           finally
           {               
                DataAccess.CloseConnection();
           }

       }
    }
}
